<?php
/**
 * Created by PhpStorm.
 * User: DongYao
 * Date: 2018/1/2
 * Time: 下午6:50
 */

namespace XmtApp\Modules\Admin\Controllers\Api;


use App\Http\Resources\AgentBalanceTransferRecordsResource;
use App\Http\Resources\AgentScoreTransferRecordsResource;
use App\Http\Resources\UserBalanceTransferRecordsResource;
use App\Tables\AgentBalanceTransferRecord;
use App\Tables\AgentScoreTransferRecord;
use App\Tables\UserBalanceTransferRecord;
use App\Tables\UserScoreTransferRecord;
use DB;
use App\Tables\User;
use Illuminate\Http\Request;

use Excel;

class ReportController extends BaseController
{
    /**
     * 按月份获取代理商收入统计报表
     * @param Request $request
     * @return \Illuminate\Http\JsonResponse
     */
    public function agentIncomeForMonth(Request $request)
    {
        /**
         * 限制查询的月份
         */
        $month = $request->get('month', date('Y-m'));

        /**
         * 查询没有收入的代理用户
         */
        $query_user = new User();
        $query_user = $query_user->select(DB::raw('id as agent_user_id, \'order_settlement\' type, 0 as total_money, 0 as order_number'));
        $query_user = $query_user->agents()->whereNotExists(function ($query) use ($month) {
            $query->select(DB::raw(1))
                ->from('agent_balance_transfer_records')
                ->whereRaw('agent_user_id = users.id')
                ->where('agent_balance_transfer_records.created_at', 'like', "{$month}%");
        })->when($request->filled('agent_user_id'), function ($query) use ($request) {
            $query->where('users.id', $request->agent_user_id);
        });

        if (!$this->user->hasRole('system') && !$this->user->hasRole('admin')) {
            $query_user = $query_user->ofChildren($this->user->id);
        }

        /**
         * 查询有收入的代理用户
         */
        $query = new AgentBalanceTransferRecord();
        $query = $query->select(DB::raw('agent_user_id, type, SUM(money) as total_money, count(*) as order_number'));

        if (!$this->user->hasRole('system') && !$this->user->hasRole('admin')) {
            $query = $query->ofChildren($this->user->id);
        }

        $query = $query->where('type', 'order_settlement');
        if ($request->filled('agent_user_id')) {
            $query = $query->where('agent_user_id', $request->agent_user_id);
        }


        /**
         * 查询月份
         */
        $query = $query->where('agent_balance_transfer_records.created_at', 'like', "{$month}%");

        $query = $query->union($query_user);

        $query = $query->orderBy('total_money', 'desc');

        $data = $query->groupBy('agent_user_id', 'type')->get();

        if ($request->get('is_export') == 'yes') {
            $info = $this->exportOrderExcel($data, "代理商收入月报表统计-{$month}");
            if (isset($info['file'])) {
                $info['url'] = url('storage/exports/' . $info['file']);
                return response()->json(ajax_return(true, '导出成功', $info));
            } else {
                return response()->json(ajax_return(false, '导出失败', [$data]));
            }
        }

        return new AgentBalanceTransferRecordsResource($data);
    }

    /**
     * 获取用户交易记录报表
     * @param Request $request
     * @return UserBalanceTransferRecordsResource
     */
    public function userBalanceRecord(Request $request)
    {
        $query = new UserBalanceTransferRecord();

        if (!$this->user->hasRole('system') && !$this->user->hasRole('admin')) {
            $query = $query->where('user_id', $this->user->id);
        }

        // 类型查询
        if ($request->filled('type')) {
            $query = $query->where('type', $request->type);
        }

        // 日期查询
        if ($request->filled('created_at')) {
            $query = $query->whereBetween('created_at', $request->created_at);
        }

        // 关键字查询
        if ($request->filled('keyword')) {
            $query = $query->where(function ($query) use ($request) {
                $query->orWhereHas('user', function ($query) use ($request) {
                    $query->where(function ($query) use ($request) {
                        $query->orWhere('name', 'like', "%{$request->keyword}%");
                        $query->orWhere('realname', 'like', "%{$request->keyword}%");
                        $query->orWhere('nickname', 'like', "%{$request->keyword}%");
                    });
                });

                $query->orWhere('order_sn', 'like', "%{$request->keyword}%");
            });
        }

        // 排序
        $query = $query->orderBy('id', 'desc');

        // 数据导出
        if ($request->get('is_export') == 'yes') {
            $export_query = $query;

            $export_data = $export_query->get();
            $info = $this->exportUserBalanceRecordsExcel($export_data, "用户账户余额报表统计");
            if (isset($info['file'])) {
                $info['url'] = url('storage/exports/' . $info['file']);
                return response()->json(ajax_return(true, '导出成功', $info));
            } else {
                return response()->json(ajax_return(false, '导出失败'));
            }
        }

        $data = $query->paginate($request->header('pageSize'));

        return new UserBalanceTransferRecordsResource($data);
    }

    /**
     * 获取用户积分交易记录报表
     * @param Request $request
     * @return UserBalanceTransferRecordsResource
     */
    public function userScoreRecord(Request $request)
    {
        $query = new UserScoreTransferRecord();

        if (!$this->user->hasRole('system') && !$this->user->hasRole('admin')) {
            $query = $query->where('user_id', $this->user->id);
        }

        // 类型查询
        if ($request->filled('type')) {
            $query = $query->where('type', $request->type);
        }

        // 日期查询
        if ($request->filled('created_at')) {
            $query = $query->whereBetween('created_at', $request->created_at);
        }

        // 关键字查询
        if ($request->filled('keyword')) {
            $query = $query->where(function ($query) use ($request) {
                $query->orWhereHas('user', function ($query) use ($request) {
                    $query->where(function ($query) use ($request) {
                        $query->orWhere('name', 'like', "%{$request->keyword}%");
                        $query->orWhere('realname', 'like', "%{$request->keyword}%");
                        $query->orWhere('nickname', 'like', "%{$request->keyword}%");
                    });
                });

                $query->orWhere('order_sn', 'like', "%{$request->keyword}%");
            });
        }

        // 排序
        $query = $query->orderBy('id', 'desc');

        // 数据导出
        if ($request->get('is_export') == 'yes') {
            $export_query = $query;
            $export_data = $export_query->get();
            $info = $this->exportUserScoreRecordsExcel($export_data, "用户账户积分交易记录");
            if (isset($info['file'])) {
                $info['url'] = url('storage/exports/' . $info['file']);
                return response()->json(ajax_return(true, '导出成功', $info));
            } else {
                return response()->json(ajax_return(false, '导出失败'));
            }
        }

        $data = $query->paginate($request->header('pageSize'));

        return new UserBalanceTransferRecordsResource($data);
    }

    /**
     * 用户账户资金变动月报表统计
     * @param Request $request
     * @return UserBalanceTransferRecordsResource|\Illuminate\Http\JsonResponse
     */
    public function userBalanceForMonth(Request $request)
    {
        /**
         * 限制查询的月份
         */
        $month = $request->get('month', date('Y-m'));

        /**
         * 查询没有收入的代理用户
         */
        $query_user = new User();
        $query_user = $query_user->select(DB::raw('id as user_id,  0 as consume, 0 as charge'));
        $query_user = $query_user->general()->whereNotExists(function ($query) use ($month) {
            $query->select(DB::raw(1))
                ->from('user_balance_transfer_records')
                ->whereRaw('user_id = users.id')
                ->where('user_balance_transfer_records.created_at', 'like', "{$month}%");
        })->when($request->filled('user_id'), function ($query) use ($request) {
            $query->where('users.id', $request->user_id);
        })->when($request->filled('province_code'), function ($query) use ($request) {
            $query->where('users.province_code', $request->province_code);
        })->when($request->filled('city_code'), function ($query) use ($request) {
            $query->where('users.city_code', $request->city_code);
        })->when($request->filled('district_code'), function ($query) use ($request) {
            $query->where('users.district_code', $request->district_code);
        });

        /**
         * 查询有收入的代理用户
         */
        $query = new UserBalanceTransferRecord();
        $query = $query->select(DB::raw('user_id, sum(case when type=\'consume\' then money else 0 end) as `consume`, sum(case when type=\'charge\' then money else 0 end) as `charge`'));

        if ($request->filled('user_id')) {
            $query = $query->where('user_id', $request->user_id);
        }

        /**
         * 查询月份
         */
        $query = $query->where('user_balance_transfer_records.created_at', 'like', "{$month}%");

        if ($request->filled('province_code')) {
            $query = $query->whereHas('user', function ($query) use ($request) {
                $query->where('province_code', $request->province_code);
            });
        }
        if ($request->filled('city_code')) {
            $query = $query->whereHas('user', function ($query) use ($request) {
                $query->where('city_code', $request->city_code);
            });
        }
        if ($request->filled('district_code')) {
            $query = $query->whereHas('user', function ($query) use ($request) {
                $query->where('district_code', $request->district_code);
            });
        }

        $query = $query->union($query_user);

        $query = $query->orderBy('user_id', 'asc');

        $data = $query->groupBy('user_id')->get();

        if ($request->get('is_export') == 'yes') {
            $info = $this->exportUserBalanceMonthExcel($data, "用户账户资金变动月报表统计-{$month}");
            if (isset($info['file'])) {
                $info['url'] = url('storage/exports/' . $info['file']);
                return response()->json(ajax_return(true, '导出成功', $info));
            } else {
                return response()->json(ajax_return(false, '导出失败', [$data]));
            }
        }

        return new UserBalanceTransferRecordsResource($data);
    }

    /**
     * 代理账户
     * @param Request $request
     * @return AgentBalanceTransferRecordsResource
     */
    public function agentBalanceRecord(Request $request)
    {
        $query = new AgentBalanceTransferRecord();

        if (!$this->user->hasRole('admin') && !$this->user->hasRole('system')) {
            $query = $query->where('agent_user_id', $this->user->id);
        }

        // 类型查询
        if ($request->filled('type')) {
            $query = $query->where('type', $request->type);
        }

        // 日期查询
        if ($request->filled('created_at')) {
            $query = $query->whereBetween('created_at', $request->created_at);
        }

        // 关键字查询
        if ($request->filled('keyword')) {
            $query = $query->where(function ($query) use ($request) {
                $query->orWhereHas('agent', function ($query) use ($request) {
                    $query->where(function ($query) use ($request) {
                        $query->orWhere('name', 'like', "%{$request->keyword}%");
                        $query->orWhere('realname', 'like', "%{$request->keyword}%");
                        $query->orWhere('nickname', 'like', "%{$request->keyword}%");
                    });
                });

                $query->orWhere('order_sn', 'like', "%{$request->keyword}%");
            });
        }

        // 排序
        $query = $query->orderBy('id', 'desc');
        $query = $query->orderBy('agent_user_id', 'asc');

        $data = $query->paginate($request->header('pageSize'));

        return new AgentBalanceTransferRecordsResource($data);
    }

    /**
     * 代理账户积分
     * @param Request $request
     * @return AgentScoreTransferRecordsResource
     */
    public function agentScoreRecord(Request $request)
    {
        $query = new AgentScoreTransferRecord();

        if (!$this->user->hasRole('admin') && !$this->user->hasRole('system')) {
            $query = $query->where('agent_user_id', $this->user->id);
        }

        // 类型查询
        if ($request->filled('type')) {
            $query = $query->where('type', $request->type);
        }

        // 日期查询
        if ($request->filled('created_at')) {
            $query = $query->whereBetween('created_at', $request->created_at);
        }

        // 关键字查询
        if ($request->filled('keyword')) {
            $query = $query->where(function ($query) use ($request) {
                $query->orWhereHas('agent', function ($query) use ($request) {
                    $query->where(function ($query) use ($request) {
                        $query->orWhere('name', 'like', "%{$request->keyword}%");
                        $query->orWhere('realname', 'like', "%{$request->keyword}%");
                        $query->orWhere('nickname', 'like', "%{$request->keyword}%");
                    });
                });

                $query->orWhere('order_sn', 'like', "%{$request->keyword}%");
            });
        }

        // 排序
        $query = $query->orderBy('id', 'desc');
        $query = $query->orderBy('agent_user_id', 'asc');

        $data = $query->paginate($request->header('pageSize'));

        return new AgentScoreTransferRecordsResource($data);
    }

    /**
     * 导出代理商收入统计数据
     * @param $list
     * @param $file_name
     * @param string $sheet_name
     * @return
     */
    private function exportOrderExcel($list, $file_name, $sheet_name = 'sheet')
    {
        $cellData = [
            ['序号','账号', '真实姓名', '身份证号', '代理级别', '上级代理',  '总金额（元）',  '单量（单）']
        ];

        $row_num =1;
        $total_money = 0;
        foreach ($list as $item) {
            $cellData[] = [
                $row_num++,
                $item->agent_name,
                $item->agent_realname,
                $item->identity_card . ' ',
                $item->agent_level_text,
                $item->parent_agent_name,
                $item->total_money,
                $item->order_number,
            ];
            $total_money = bcadd($total_money, $item->total_money, 2);
        }

        $cellData[] = [
            $row_num++,
            '',
            '',
            '',
            '',
            '合计：',
            $total_money,
            '',
        ];


        return Excel::create($file_name, function($excel) use ($cellData, $row_num, $sheet_name){
            $excel->sheet($sheet_name, function($sheet) use ($cellData, $row_num){
                $sheet->setFreeze('A2');

                $sheet->setWidth(array(
                    'A' => 5,
                    'B' => 20,
                    'C' => 20,
                    'D' => 20,
                    'E' => 20,
                    'F' => 20,
                ));

                $sheet->setColumnFormat(array(
                    'A' => '@',
                    'B' => '@',
                    'C' => '@',
                    'D' => '@',
                    'E' =>  '#,##0.00',
                    'F' => '@',
                ));

                $sheet->setAutoSize(false);

                $sheet->rows($cellData);

                $sheet->cells('A1:J1', function($cells) {
                    $cells->setBackground('#3C9017');
                    $cells->setFontColor('#ffffff');
                    $cells->setAlignment('center');
                });

                $sheet->cells('A1:J' . $row_num, function($cells) {
                    $cells->setBorder('solid', 'solid', 'solid', 'solid');
                    $cells->setAlignment('center');
                });

                $sheet->cells("A{$row_num}:J{$row_num}", function($cells) {
                    $cells->setBackground('#cccc33');
                    $cells->setFontColor('#cc0000');
                });
            });
        })->save('xls', false, true);
    }

    /**
     * 导出代理商收入统计数据
     * @param $list
     * @param $file_name
     * @param string $sheet_name
     * @return
     */
    private function exportUserBalanceRecordsExcel($list, $file_name, $sheet_name = 'sheet')
    {
        $cellData = [
            ['序号','订单编号', '用户账号', '交易类型', '交易金额（元）', '交易后账户余额（元）',  '交易时间']
        ];

        $row_num =1;
        foreach ($list as $item) {
            $cellData[] = [
                $row_num++,
                $item->order_sn,
                $item->username,
                $item->type_text,
                $item->money,
                $item->balance,
                $item->created_at,
            ];
        }

        return Excel::create($file_name, function($excel) use ($cellData, $row_num, $sheet_name){
            $excel->sheet($sheet_name, function($sheet) use ($cellData, $row_num){
                $sheet->setFreeze('A2');

                $sheet->setWidth(array(
                    'A' => 5,
                    'B' => 25,
                    'C' => 20,
                    'D' => 20,
                    'E' => 20,
                    'F' => 20,
                    'G' => 20,
                ));

                $sheet->setColumnFormat(array(
                    'A' => '@',
                    'B' => '@',
                    'C' => '@',
                    'D' => '@',
                    'E' => '#,##0.00',
                    'F' => '#,##0.00',
                    'G' => '@',
                ));

                $sheet->setAutoSize(false);

                $sheet->rows($cellData);

                $sheet->cells('A1:J1', function($cells) {
                    $cells->setBackground('#3C9017');
                    $cells->setFontColor('#ffffff');
                    $cells->setAlignment('center');
                });

                $sheet->cells('A1:J' . $row_num, function($cells) {
                    $cells->setBorder('solid', 'solid', 'solid', 'solid');
                    $cells->setAlignment('center');
                });
            });
        })->save('xls', false, true);
    }

    /**
     * 导出代理商收入统计数据
     * @param $list
     * @param $file_name
     * @param string $sheet_name
     * @return
     */
    private function exportUserScoreRecordsExcel($list, $file_name, $sheet_name = 'sheet')
    {
        $cellData = [
            ['序号','订单编号', '用户账号', '交易类型', '交易积分数量（个）', '交易后积分账户余额（个）',  '交易时间']
        ];

        $row_num =1;
        foreach ($list as $item) {
            $cellData[] = [
                $row_num++,
                $item->order_sn,
                $item->username,
                $item->type_text,
                $item->score,
                $item->total_score,
                $item->created_at,
            ];
        }

        return Excel::create($file_name, function($excel) use ($cellData, $row_num, $sheet_name){
            $excel->sheet($sheet_name, function($sheet) use ($cellData, $row_num){
                $sheet->setFreeze('A2');

                $sheet->setWidth(array(
                    'A' => 5,
                    'B' => 25,
                    'C' => 20,
                    'D' => 20,
                    'E' => 20,
                    'F' => 20,
                    'G' => 20,
                ));

                $sheet->setColumnFormat(array(
                    'A' => '@',
                    'B' => '@',
                    'C' => '@',
                    'D' => '@',
                    'E' => '@',
                    'F' => '@',
                    'G' => '@',
                ));

                $sheet->setAutoSize(false);

                $sheet->rows($cellData);

                $sheet->cells('A1:J1', function($cells) {
                    $cells->setBackground('#3C9017');
                    $cells->setFontColor('#ffffff');
                    $cells->setAlignment('center');
                });

                $sheet->cells('A1:J' . $row_num, function($cells) {
                    $cells->setBorder('solid', 'solid', 'solid', 'solid');
                    $cells->setAlignment('center');
                });
            });
        })->save('xls', false, true);
    }

    /**
     * 导出用户账户资金变动月报表统计
     * @param $list
     * @param $file_name
     * @param string $sheet_name
     * @return
     */
    private function exportUserBalanceMonthExcel($list, $file_name, $sheet_name = 'sheet')
    {
        $cellData = [
            ['序号', '用户ID', '用户账号', '所在地', '消费总金额（元）', '充值总金额（元）']
        ];

        $row_num =1;
        foreach ($list as $item) {
            $cellData[] = [
                $row_num++,
                $item->user_id,
                $item->username,
                $item->region_text,
                $item->consume,
                $item->charge,
            ];
        }

        return Excel::create($file_name, function($excel) use ($cellData, $row_num, $sheet_name){
            $excel->sheet($sheet_name, function($sheet) use ($cellData, $row_num){
                $sheet->setFreeze('A2');

                $sheet->setWidth(array(
                    'A' => 5,
                    'B' => 25,
                    'C' => 25,
                    'D' => 60,
                    'E' => 20,
                    'F' => 20,
                ));

                $sheet->setColumnFormat(array(
                    'A' => '@',
                    'B' => '@',
                    'C' => '@',
                    'D' => '@',
                    'E' => '#,##0.00',
                    'F' => '#,##0.00',
                ));

                $sheet->setAutoSize(false);

                $sheet->rows($cellData);

                $sheet->cells('A1:J1', function($cells) {
                    $cells->setBackground('#3C9017');
                    $cells->setFontColor('#ffffff');
                    $cells->setAlignment('center');
                });

                $sheet->cells('A1:J' . $row_num, function($cells) {
                    $cells->setBorder('solid', 'solid', 'solid', 'solid');
                    $cells->setAlignment('center');
                });
            });
        })->save('xls', false, true);
    }
}